package com.commute.data;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;



public class DataStructure2 {
	public static final boolean printoutActive = false;
	
	public LinkedList<Stop> stops;
	public LinkedList<Route> routes;
	public LinkedList<Trip> trips;
	public LinkedList<StopTime> stopTimes;
	
	public static void main(String[] args) {
		for (int i = 0; i < args.length; i++) {
			
		}
		new DataStructure2();
	}
	
	public DataStructure2(){
		stops = new LinkedList<Stop>();
		routes = new LinkedList<Route>();
		trips = new LinkedList<Trip>();
		stopTimes = new LinkedList<StopTime>();
		
		try {
			stopMaker();
			routeMaker();
			tripMaker();
			stopTimeMaker();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		
		stopAdder();
		
		for (Route r : routes) {
			combine(r);
		}
	}
	
	
	private void stopMaker()  throws ClassNotFoundException {															// LAT 			LON
		Class.forName("org.sqlite.JDBC");

		Connection connection = null;
		try
		{
			// create a database connection
			connection = DriverManager.getConnection("jdbc:sqlite:LLT_test_data.db");
			Statement statement = connection.createStatement();
			statement.setQueryTimeout(30);  // set timeout to 30 sec.

			ResultSet rs;
			if(printoutActive){
				System.out.println("------");
				System.out.println("Stops");
				System.out.println("id \tname \t\t\tlat \t\tlon");
			}
			rs = statement.executeQuery("select * from stops");
			while(rs.next())
			{
				// read the result set
				if(printoutActive){
					if(rs.getString("stop_name").length()<8){
						System.out.println(rs.getInt("stop_id") + 
								"\t" + rs.getString("stop_name") +
								"\t\t\t" + rs.getDouble("stop_lat") +
								"\t" + rs.getDouble("stop_lon"));
					} else if(rs.getString("stop_name").length()<16) {
						System.out.println(rs.getInt("stop_id") + 
								"\t" + rs.getString("stop_name") +
								"\t\t" + rs.getDouble("stop_lat") +
								"\t" + rs.getDouble("stop_lon"));
					} else {
						System.out.println(rs.getInt("stop_id") + 
								"\t" + rs.getString("stop_name") +
								"\t" + rs.getDouble("stop_lat") +
								"\t" + rs.getDouble("stop_lon"));
					}
				}
				
				stops.add(new Stop(rs.getString("stop_name"), rs.getInt("stop_id"), rs.getDouble("stop_lat"), rs.getDouble("stop_lon")));
			}
			if(printoutActive) System.out.println("------");

		}
		catch(SQLException e)
		{
			// if the error message is "out of memory", 
			// it probably means no database file is found
			System.err.println(e.getMessage());
		}
		finally
		{
			try
			{
				if(connection != null)
					connection.close();
			}
			catch(SQLException e)
			{
				// connection close failed.
				System.err.println(e);
			} 
		}
	}
	
	private void routeMaker()  throws ClassNotFoundException {															// LAT 			LON
		Class.forName("org.sqlite.JDBC");

		Connection connection = null;
		try
		{
			// create a database connection
			connection = DriverManager.getConnection("jdbc:sqlite:LLT_test_data.db");
			Statement statement = connection.createStatement();
			statement.setQueryTimeout(30);  // set timeout to 30 sec.

			ResultSet rs;

			if(printoutActive){
				System.out.println("------");
				System.out.println("Routes");
				System.out.println("id \tshort_name \tlong_name \ttype");
			}
			rs = statement.executeQuery("select * from routes");
			while(rs.next())
			{
				// read the result set
				if(printoutActive){
					System.out.println(rs.getInt("route_id") + 
							"\t" + rs.getString("route_short_name") + 
							"\t\t" + rs.getString("route_long_name") + 
							"\t\t" + rs.getInt("route_type"));
				}
				routes.add(new Route(rs.getString("route_short_name"), rs.getInt("route_id")));
			}
			if(printoutActive) System.out.println("------");

			
		}
		catch(SQLException e)
		{
			// if the error message is "out of memory", 
			// it probably means no database file is found
			System.err.println(e.getMessage());
		}
		finally
		{
			try
			{
				if(connection != null)
					connection.close();
			}
			catch(SQLException e)
			{
				// connection close failed.
				System.err.println(e);
			} 
		}
	}
	
	/*
	 * routeMaker must have been run before.
	 */
	private void tripMaker()  throws ClassNotFoundException {															// LAT 			LON
		Class.forName("org.sqlite.JDBC");

		Connection connection = null;
		try
		{
			// create a database connection
			connection = DriverManager.getConnection("jdbc:sqlite:LLT_test_data.db");
			Statement statement = connection.createStatement();
			statement.setQueryTimeout(30);  // set timeout to 30 sec.

			ResultSet rs;

			if(printoutActive){
				System.out.println("------");
				System.out.println("Trips");
				System.out.println("route_id \tservice_id \ttrip_id");
			}
			rs = statement.executeQuery("select * from trips");
			while(rs.next())
			{
				if(printoutActive){
					System.out.println(rs.getInt("route_id") + 
							"\t\t" + rs.getInt("service_id") + 
							"\t\t" + rs.getInt("trip_id"));
				}
				Route route = null;
				for (Route r : routes) {
					if(r.id == rs.getInt("route_id")){
						route = r;
						break;
					}
				}
				trips.add(new Trip(rs.getInt("trip_id"), rs.getInt("route_id"), route));
			}
			if(printoutActive) System.out.println("------");

			
		}
		catch(SQLException e)
		{
			// if the error message is "out of memory", 
			// it probably means no database file is found
			System.err.println(e.getMessage());
		}
		finally
		{
			try
			{
				if(connection != null)
					connection.close();
			}
			catch(SQLException e)
			{
				// connection close failed.
				System.err.println(e);
			} 
		}
	}
	
	private void stopTimeMaker()  throws ClassNotFoundException {															// LAT 			LON
		Class.forName("org.sqlite.JDBC");

		Connection connection = null;
		try
		{
			// create a database connection
			connection = DriverManager.getConnection("jdbc:sqlite:LLT_test_data.db");
			Statement statement = connection.createStatement();
			statement.setQueryTimeout(30);  // set timeout to 30 sec.

			ResultSet rs;

			if(printoutActive){
				System.out.println("------");
				System.out.println("Stop_times");
				System.out.println("trip_id \tarrival_time \tdeparture_time \tstop_id \tstop_sequence");
			}
			rs = statement.executeQuery("select * from stop_times");
			while(rs.next())
			{
				if(printoutActive){
					System.out.println(rs.getInt("trip_id") + 
							"\t\t" + rs.getString("arrival_time") +
							"\t" + rs.getString("departure_time") +
							"\t" + rs.getInt("stop_id") +
							"\t\t" + rs.getInt("stop_sequence"));
				}
				Trip trip = null;
				for (Trip t : trips) {
					if(t.tripId() == rs.getInt("trip_id")){
						trip = t;
						break;
					}
				}
				Stop stop = null;
				for (Stop s : stops) {
					if (s.id == rs.getInt("stop_id")) {
						stop = s;
						break;
					}
				}
				stopTimes.add(new StopTime(rs.getInt("trip_id"), trip, rs.getInt("stop_id"), stop, rs.getInt("stop_sequence"), rs.getString("arrival_time"), rs.getString("departure_time")));
			}
			if(printoutActive) System.out.println("------");

			
		}
		catch(SQLException e)
		{
			// if the error message is "out of memory", 
			// it probably means no database file is found
			System.err.println(e.getMessage());
		}
		finally
		{
			try
			{
				if(connection != null)
					connection.close();
			}
			catch(SQLException e)
			{
				// connection close failed.
				System.err.println(e);
			} 
		}
	}
	
	private void stopAdder(){

		for (StopTime stopTime : stopTimes) {
			if(!stopTime.trip().stopTimes().contains(stopTime)){
				stopTime.trip().stopTimes().add(stopTime);
			}
			if (!stopTime.trip().route().trips.contains(stopTime.trip())) {
				stopTime.trip().route().trips.add(stopTime.trip());
			}
			if(!stopTime.trip().route().stops.contains(stopTime.stop())){
				stopTime.trip().route().stops.add(stopTime.stop());
			}
		}
		
		for (Trip trip : this.trips){
			StopTime.sort(trip.stopTimes());
		}
		
		// test that it works:
		if(printoutActive){
			for (Route route : routes) {
				for (Stop stop : route.stops) {
					System.out.println(route.id+": "+stop.id);
				}
				for (Trip trip : route.trips) {
					System.out.println(route.id+": "+trip.tripId());
					for (StopTime st : trip.stopTimes()) {
						System.out.println(trip.tripId()+" "+st.stopId());
					}
				}
			}
		}
		
		
	}
	
	private void combine(Route r){
		for (Stop s : stops) {
			if(r.stops.contains(s)){
				s.routes.add(r.id);
			}
		}
	}
	
}
